Imports AUtility
Partial Public Class Statistika
    Inherits System.Web.UI.Page

    Dim selectStr As String = ""

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        Ukupno()
        NajviseOdigranih()
        Sudjelovalo()
        NajvisePobjeda()
        NajboljiOmjer()
        NajviseOdigranihPrijateljskih()

        Ranking_Ljeto_2009()

        Ranking_Zima2009_2010()
        'If Request.RawUrl.IndexOf("localhost") < 0 Then
        '    hypHeadToHead.NavigateUrl = "http://tenisliga.blogspot.com/2001/03/head-to-head.html"
        'End If

    End Sub

    Private Sub Ukupno()
        lblUkupno.Text = DataAccess.GetString(GlobalTenisLiga.ConnectionString, "SELECT Count(*) FROM Susret")
        lblUkupnoLiga.Text = DataAccess.GetString(GlobalTenisLiga.ConnectionString, "SELECT Count(*) FROM Susret WHERE SezonaId <> 7")
        lblUkupnoPrijateljske.Text = DataAccess.GetString(GlobalTenisLiga.ConnectionString, "SELECT Count(*) FROM Susret WHERE SezonaId = 7")
    End Sub

    Private Sub NajviseOdigranih()

        selectStr = String.Format("SELECT TOP 25 vIgrac.*, vOdigrane.Odigrane FROM vIgrac JOIN vOdigrane on vIgrac.IgracId = vOdigrane.IgracId ORDER BY Odigrane DESC")
        gvOdigrane.DataSource = AddRedniBroj((DataAccess.GetDataTable(GlobalTenisLiga.ConnectionString, selectStr)))
        gvOdigrane.DataBind()

    End Sub

    Private Sub NajvisePobjeda()

        selectStr = String.Format("SELECT TOP 25 vIgrac.*, vPobjede.Pobjede FROM vIgrac JOIN vPobjede on vIgrac.IgracId = vPobjede.IgracId ORDER BY Pobjede DESC")
        gvPobjede.DataSource = AddRedniBroj((DataAccess.GetDataTable(GlobalTenisLiga.ConnectionString, selectStr)))
        gvPobjede.DataBind()

    End Sub

    Private Sub Sudjelovalo()
        lblSudjelovalo.Text = DataAccess.GetString(GlobalTenisLiga.ConnectionString, "SELECT Count(*) FROM Igrac")
    End Sub

    Private Sub NajboljiOmjer()

        selectStr = String.Format("SELECT TOP 25 vIgrac.*, vOdigrane.Odigrane, vPobjede.Pobjede, ROUND( 100*CONVERT(float,Pobjede) /CONVERT(float,Odigrane), 0) AS Omjer  FROM vIgrac JOIN vPobjede ON vIgrac.IgracId = vPobjede.IgracId JOIN vOdigrane ON vIgrac.IgracId = vOdigrane.IgracId WHERE Aktivan = 1 ORDER BY Omjer DESC, Odigrane DESC")
        gvOmjer.DataSource = AddRedniBroj(DataAccess.GetDataTable(GlobalTenisLiga.ConnectionString, selectStr))
        gvOmjer.DataBind()

    End Sub

    Private Sub Ranking_Ljeto_2009()
        Try
            selectStr = String.Format("SELECT ImePrezime, IgracId, 100*ROUND(Cast(SUM(BodLiga) as float)/COUNT(*), 2) AS Ranking FROM vRanking WHERE SezonaId > 8 AND SezonaId < 15 GROUP BY ImePrezime,IgracId ORDER BY Ranking DESC")
            If (gvRenking Is Nothing) Then
                gvRenking = New GridView
            End If
            gvRenking.DataSource = AddRedniBroj(DataAccess.GetDataTable(GlobalTenisLiga.ConnectionString, selectStr))
            gvRenking.DataBind()
        Catch ex As Exception
            Response.Write(ex.Message)
        End Try
    End Sub

    Private Sub Ranking_Zima2009_2010()
        Try
            selectStr = String.Format("SELECT ImePrezime, IgracId, 100*ROUND(Cast(SUM(BodLiga) as float)/COUNT(*), 2) AS Ranking FROM vRanking WHERE SezonaId > 14 GROUP BY ImePrezime,IgracId ORDER BY Ranking DESC")
            If (gvRenking2 Is Nothing) Then
                gvRenking2 = New GridView
            End If
            gvRenking2.DataSource = AddRedniBroj(DataAccess.GetDataTable(GlobalTenisLiga.ConnectionString, selectStr))
            gvRenking2.DataBind()
        Catch ex As Exception
            Response.Write(ex.Message)
        End Try
    End Sub

    Public Function AddRedniBroj(ByVal dt As DataTable) As DataTable
        Dim i As Integer = 0
        dt.Columns.Add("RedniBroj")
        For Each row As DataRow In dt.Rows
            i = i + 1
            row("RedniBroj") = i
        Next
        Return dt
    End Function

    Private Sub NajviseOdigranihPrijateljskih()

        selectStr = String.Format("SELECT TOP 25 vIgrac.*, vOdigranePrijateljske.Odigrane FROM vIgrac JOIN vOdigranePrijateljske on vIgrac.IgracId = vOdigranePrijateljske.IgracId ORDER BY Odigrane DESC")
        gvOdigranePrijateljske.DataSource = AddRedniBroj((DataAccess.GetDataTable(GlobalTenisLiga.ConnectionString, selectStr)))
        gvOdigranePrijateljske.DataBind()

    End Sub

End Class